﻿Imports Microsoft.VisualBasic
Imports System.Globalization
Imports System.Diagnostics
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient


Public Class KurzCNB
    Public Function nactiAktualniKurz(ByVal mena As String) As Single
        Dim kurz As Single
        Dim data As String
        Dim radka() As String
        Dim c As New System.Net.WebClient
        Dim dataURL As String = String.Format("http://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.txt?date={0:dd.MM.yyyy}", Today.Date)

        c.Encoding = Text.Encoding.UTF8
        data = c.DownloadString(dataURL)

        Dim radky() As String = data.Split(New Char() {CChar(vbNewLine), CChar(vbLf)})

        For ind As Integer = 2 To radky.Length - 1
            radka = radky(ind).Split("|")
            If radka(3) = mena Then
                If CultureInfo.CurrentCulture.ToString = "en-US" Then radka(4) = Replace(radka(4), ",", ".")
                kurz = Single.Parse(radka(4), CultureInfo.CurrentCulture.NumberFormat)
                Exit For
            End If
        Next
        Return kurz
    End Function
    Public Function nactiUlozenyKurz(ByVal mena As String) As Single
        Dim kurz As Single
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("DataHorejsi").ConnectionString
        Dim dbConnection As System.Data.SqlClient.SqlConnection = New SqlConnection(connectionString)
        Dim myCommand As SqlCommand = New SqlCommand
        Dim mydataSet As New DataSet
        Dim dataAdapter As SqlDataAdapter
        Dim myRow As DataRow


        dbConnection.Open()
        myCommand.Connection = dbConnection


        myCommand.CommandText = String.Format("SELECT T_Parametry.Nazev as Mena, T_Parametry.Popis, T_ParametryValue.real_value as kurz FROM T_ParametrTyp INNER JOIN" _
                      & " T_Parametry ON T_ParametrTyp.ID_typ = T_Parametry.ID_typ INNER JOIN" _
                      & " T_ParametryValue ON T_ParametrTyp.ID_typ = T_ParametryValue.ID_typ AND T_Parametry.ID_parametr = T_ParametryValue.ID_parametr" _
                      & " WHERE (T_Parametry.Nazev = '{0}')", mena)
        myCommand.CommandType = CommandType.Text

        dataAdapter = New SqlDataAdapter(myCommand.CommandText, dbConnection)

        dataAdapter.Fill(mydataSet, "Kurzy")


        myRow = mydataSet.Tables("Kurzy").Rows(0)


        kurz = myRow("kurz")

        dbConnection.Close()

        Return kurz
    End Function
    Public Function ulozKurz(ByVal mena As String, ByVal hodnota As Single) As Integer
        Dim retVal As Integer
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("DataHorejsi").ConnectionString
        Dim dbConnection As System.Data.SqlClient.SqlConnection = New SqlConnection(connectionString)
        Dim myCommand As SqlCommand = New SqlCommand


        dbConnection.Open()
        myCommand.Connection = dbConnection

        myCommand.CommandText = "UPDATE T_ParametryValue SET real_value = @hodnota FROM T_ParametryValue INNER JOIN" _
                  & " T_Parametry ON T_ParametryValue.ID_parametr = T_Parametry.ID_parametr" _
                  & " WHERE (T_Parametry.Nazev = @mena)"
        myCommand.CommandType = CommandType.Text

        myCommand.Parameters.AddWithValue("@hodnota", hodnota)
        myCommand.Parameters.AddWithValue("@mena", mena)

        retVal = myCommand.ExecuteNonQuery()
        dbConnection.Close()
        Return retVal
    End Function

End Class
